/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;


/**
 *
 * @author Haqim
 */
public class dbFunctions {
    
    
    
    
    
    public static void insertStudent( ArrayList<String> students){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        for ( int i = 0 ; i < students.size() ; i++ ){
            
            String[] data = students.get(i).split(",");
            
            try {
                
                conn = ConnectionManager.getConnection();
                stmt = conn.createStatement();
                
                
                String key = (" SET FOREIGN_KEY_CHECKS=0;");
                System.out.println("Foreign key on "+ stmt.executeUpdate(key));
                
                //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
                String insert = ("INSERT INTO  `BOSS`.`student` (\n" +
                        "`userid` ,\n" +
                        "`password` ,\n" +
                        "`name` ,\n" +
                        "`school` ,\n" +
                        "`edollar`\n" +
                        ")\n" +
                        "VALUES (\n" +
                        "'"+ data[0]+"',  '"+ data[1]+"',  '"+data[2]+"',  '"+data[3]+"',  '"+data[4]+"'\n" +
                        ");");
                
                System.out.println(insert);
                
                System.out.println("Insert student result: "+ stmt.executeUpdate(insert));
                
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("student");
            } finally {
                ConnectionManager.close(conn, stmt, rs);
            }
            
        }
        
    }
    
    public static void insertCourseCompleted( ArrayList<String> students){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        for ( int i = 0 ; i < students.size() ; i++ ){
            
            String[] data = students.get(i).split(",");
            
            try {
                conn = ConnectionManager.getConnection();
                stmt = conn.createStatement();
                
                String key = (" SET FOREIGN_KEY_CHECKS=0;");
                System.out.println("Foreign key on "+ stmt.executeUpdate(key));
                //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
                String insert = ("INSERT INTO  `BOSS`.`courseCompleted` (\n" +
                        "`userid` ,\n" +
                        "`courseCode`\n" +
                        ")\n" +
                        "VALUES (\n" +
                        "'"+ data[0]+"',  '"+ data[1]+"'"+
                        ");");
                
                System.out.println(insert);
                
                System.out.println("Insert into course completed result: "+ stmt.executeUpdate(insert));
                
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("course completed");
            } finally {
                ConnectionManager.close(conn, stmt, rs);
            }
            
        }
        
    }
    
    public static void insertCourse( ArrayList<Course> students){
        Connection conn = null;
        PreparedStatement stmt = null;
        Statement stmt2 = null;
        ResultSet rs = null;
        
        System.out.println("i: " + students.size());
        
        for ( int i = 0 ; i < students.size() ; i++ ){
            
            //System.out.println(i + "pos");
            //System.out.println(students.get(i));
            
            Course data = (Course) students.get(i);
            
            //System.out.println(i + ": " + students.get(i));
            
            //if( data[0] != null || !data[0].isEmpty() || !data[0].equals("") || data.length != 0 ){
            String insert = "";
            try {
                conn = ConnectionManager.getConnection();
                stmt2 = conn.createStatement();
                
                //                String key = (" SET FOREIGN_KEY_CHECKS=0;");
                //            System.out.println("Foreign key on "+ stmt.executeQuery("SET FOREIGN_KEY_CHECKS=0"));
                //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
                stmt = conn.prepareStatement("INSERT INTO  `BOSS`.`course` (\n" +
                        "`course` ,\n" +
                        "`school` ,\n" +
                        "`course_name` ,\n" +
                        "`description` ,\n" +
                        "`exam` ,\n" +
                        "`examStart` ,\n" +
                        "`examEnd`\n" +
                        ")\n" +
                        " VALUES (?,?,?,?,?,?,?);");
                
                
                stmt.setString(1, data.getCourse());
                stmt.setString(2, data.getSchool());
                stmt.setString(3, data.getTitle());
                stmt.setString(4, data.getDesc());
                stmt.setString(5, data.getExamDate());
                stmt.setString(6, data.getExamStart());
                stmt.setString(7, data.getExamEnd());
                
                
                
                //System.out.println(stmt.toString());
                System.out.println("Insert boss course result: "+ stmt.executeUpdate());
                
            } catch ( ArrayIndexOutOfBoundsException k  ){
                System.out.println("Error for Array");
                System.out.println(stmt.toString());
            } catch (SQLException e) {
                
                e.printStackTrace();
                System.out.println("course results");
                System.out.println(stmt.toString());
            } finally {
                ConnectionManager.close(conn, stmt, rs);
            }
            //}
            
        }
        
    }
    
    public static void insertPrerequisites( ArrayList<String> students){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        for ( int i = 0 ; i < students.size() ; i++ ){
            
            String[] data = students.get(i).split(",");
            
            try {
                conn = ConnectionManager.getConnection();
                stmt = conn.createStatement();
                
                String key = (" SET FOREIGN_KEY_CHECKS=0;");
                System.out.println("Foreign key on "+ stmt.executeUpdate(key));
                //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
                String insert = ("INSERT INTO  `BOSS`.`prerequisites` (\n" +
                        "`masterCourse` ,\n" +
                        "`dependentCourse`\n" +
                        ")\n" +
                        "VALUES (\n" +
                        "'"+ data[0]+"',  '"+ data[1]+"' "+
                        ");");
                
                System.out.println(insert);
                
                System.out.println("Insert prerequisite result: "+ stmt.executeUpdate(insert));
                
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("prerequisite");
            } finally {
                ConnectionManager.close(conn, stmt, rs);
            }
            
        }
        
    }
    
    public static void insertSection( ArrayList<String> students){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        for ( int i = 0 ; i < students.size() ; i++ ){
            
            String[] data = students.get(i).split(",");
            
            try {
                
                
                
                conn = ConnectionManager.getConnection();
                stmt = conn.createStatement();
                
                String key = (" SET FOREIGN_KEY_CHECKS=0;");
                System.out.println("Foreign key on "+ stmt.executeUpdate(key));
                //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
                String insert = ("INSERT INTO  `BOSS`.`section` (\n" +
                        "`course` ,\n" +
                        "`section` ,\n" +
                        "`day` ,\n" +
                        "`start_time` ,\n" +
                        "`end_time` ,\n" +
                        "`instructor` ,\n" +
                        "`venue` ,\n" +
                        "`size`\n" +
                        ")\n" +
                        "VALUES (\n" +
                        "'"+ data[0]+"',  '"+ data[1]+"',  '"+data[2]+"', "
                        + " '"+data[3]+"',  '"+data[4]+"',  '"+data[5]+"', "
                        + " '"+data[6]+"',  '"+data[7]+"'\n" +
                        ");");
                
                System.out.println(insert);
                
                System.out.println("Insert into boss section result: "+ stmt.executeUpdate(insert));
                
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("section");
            } finally {
                ConnectionManager.close(conn, stmt, rs);
            }
            
        }
        
    }
    public static void insertBid( ArrayList<String> students){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        for ( int i = 0 ; i < students.size() ; i++ ){
            
            String[] data = students.get(i).split(",");
            
            try {
                conn = ConnectionManager.getConnection();
                stmt = conn.createStatement();
                
                
                String key = (" SET FOREIGN_KEY_CHECKS=0;");
                System.out.println("Foreign key on "+ stmt.executeUpdate(key));
                //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
                String insert = ("INSERT INTO  `BOSS`.`bid` (\n" +
                        "`userid` ,\n" +
                        "`courseCode` ,\n" +
                        "`section` ,\n" +
                        "`amount`\n" +
                        ")\n" +
                        "VALUES (\n" +
                        "'"+ data[0]+"',  '"+ data[2]+"',  '"+data[3]+"', "
                        + " '"+data[1]+"'\n" +
                        ");");
                
                System.out.println(insert);
                
                System.out.println("Insert into boss bid result: "+ stmt.executeUpdate(insert));
                
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("bid");
            } finally {
                ConnectionManager.close(conn, stmt, rs);
            }
            
        }
        
    }
    
    
    public static void dropTables( ){
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        
        
        try {
            conn = ConnectionManager.getConnection();
            stmt = conn.createStatement();
            
            
            //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
            String key = (" SET FOREIGN_KEY_CHECKS=0;");
            System.out.println("Foreign key off: "+ stmt.executeUpdate(key));
            
            String bid = (" TRUNCATE TABLE `bid`");
            System.out.println("Drop bid: "+ stmt.executeUpdate(bid));
            
            
            
            
            
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        try {
            conn = ConnectionManager.getConnection();
            stmt = conn.createStatement();
            //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
            
            String key = (" SET FOREIGN_KEY_CHECKS=0;");
            System.out.println("Foreign key on "+ stmt.executeUpdate(key));
            
            String courseCompleted = ("TRUNCATE TABLE `courseCompleted`");
            System.out.println("Drop Course Completed: "+ stmt.executeUpdate(courseCompleted));
            
            
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        try {
            conn = ConnectionManager.getConnection();
            stmt = conn.createStatement();
            //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
            
            String key = (" SET FOREIGN_KEY_CHECKS=0;");
            System.out.println("Foreign key on "+ stmt.executeUpdate(key));
            
            String prerequisite = ("TRUNCATE TABLE `prerequisites`");
            System.out.println("Drop Prerequisites: "+ stmt.executeUpdate(prerequisite));
            
            
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        try {
            conn = ConnectionManager.getConnection();
            stmt = conn.createStatement();
            //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
            
            String key = (" SET FOREIGN_KEY_CHECKS=0;");
            System.out.println("Foreign key on "+ stmt.executeUpdate(key));
            
            String section = ("Truncate TABLE `section`");
            System.out.println("Drop Section: "+ stmt.executeUpdate(section));
            
            
            
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        try {
            conn = ConnectionManager.getConnection();
            stmt = conn.createStatement();
            //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
            
            String key = (" SET FOREIGN_KEY_CHECKS=0;");
            System.out.println("Foreign key on "+ stmt.executeUpdate(key));
            
            String course = ("TRUNCATE TABLE `course`");
            System.out.println("Drop Course: "+ stmt.executeUpdate(course));
            
            
            
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        try {
            conn = ConnectionManager.getConnection();
            stmt = conn.createStatement();
            //String sqlstatement = "INSERT INTO contact (given_name,family_name,email,address,postal) VALUES('" + givenName + "','" + familyName + "','" + email + "','" + address + "','" + postal + "')";
            
            String key = (" SET FOREIGN_KEY_CHECKS=0;");
            System.out.println("Foreign key on "+ stmt.executeUpdate(key));
            
            
            String student = ("TRUNCATE TABLE `student`");
            System.out.println("Drop student: "+ stmt.executeUpdate(student));
            
            
            String key2 = (" SET FOREIGN_KEY_CHECKS=1;");
            System.out.println("Foreign key on "+ stmt.executeUpdate(key2));
            
            
        } catch (SQLException e) {
            e.printStackTrace() ;
        } finally {
            ConnectionManager.close(conn, stmt, rs);
        }
        
        
        
    }
    
}
